--************ Checking Number of Available leads *********************************
Declare @OldStartDate varchar(8)
Declare @OldEndDate varchar(8)
Declare @LeadStartDate varchar(8)
Declare @LeadEndDate varchar(8)

SET  @LeadStartDate = Convert(varchar(8), DateAdd(day, -120, GetDate()), 112)
SET  @LeadEndDate = Convert(varchar(8), GetDate(), 112)

SET @OldStartDate = '20120425'
SET @OldEndDate = '20120425'

--select CEID, Email, IsReject from tblCEUser
--Where (Convert(varchar(8), CreatedDate, 112) Between @OldStartDate And @OldEndDate)
--And (IsReject IS NULL OR IsReject = 0) And UsrTypeID = 400
--And CEID NOT IN (Select CustomerID from tblCXCRMAgentLeads Where (Convert(varchar(8), CreatedDate, 112) Between @LeadStartDate And @LeadEndDate))
--And CEID NOT IN (select RefCEUserID  from tblNonGGCarMoreDetails)
--Order By CEID

  --********** Assigning to agent (Same like auto lead assignement logic) **********************************************

	Declare @CarID bigint
	Declare @AgentID bigint
	Declare @CustomerID bigint

	--SELECT TOP 1 @AgentID = CEID from tblCEUser Where CEID IN (547, 18808, 8543, 18813, 8546, 18818) Order By NewID()

	Declare @Broker1PendingCount int
	Declare @Broker8PendingCount int

	DECLARE @@tblAgentPendingLead TABLE 
	(
		AgentID bigint,
		[Status] smallint,
		[AgentType] varchar(3),
		[PendingCount] int
	)
	
	DECLARE @@tblTempAgentPendingLead TABLE 
	(
		AgentID bigint,
		[Status] smallint,
		[AgentType] varchar(3),
		[PendingCount] int
	)
	
	--Get Temp Counting (May not include all agentID)
	INSERT INTO @@tblTempAgentPendingLead (AgentID, [Status], [AgentType], [PendingCount])
	select l.AgentID, l.[Status], u.Info1 AS AgentType, Count(l.AgentLeadID) AS [PendingCount] 
	FROM tblCXCRMAgentLeads l 
	INNER JOIN tblCEUser u ON l.AgentID = u.CEID
	Where u.[Status] = 1 AND u.UsrTypeID = 200 AND (u.Info1 = 'ONSS' OR u.Info1 = 'ONJ')
	AND (l.[Status] = 1)
	-- And l.IsFromService = 2 And Convert(varchar(8), l.CreatedDate, 112) > '20121101'
	And (l.CallType = 100 OR l.CallType = 101) 
	--And l.AgentID IN (45053, 4425, 547, 18808, 8543, 18813, 18818, 6807)
	And l.CustomerID > 0
	Group By l.AgentID, l.[Status], u.Info1
	
	--Create All Agent PendindCount = 0
	INSERT INTO @@tblAgentPendingLead (AgentID, [Status], [AgentType], [PendingCount])
	select CEID, 1, Info1,  0 From tblCEUser 
	--WHERE CEID IN (45053, 4425, 547, 18808, 8543, 18813, 18818, 6807)
	WHERE [Status] = 1 AND UsrTypeID = 200 AND (Info1 = 'ONSS' OR Info1 = 'ONJ')
	
	--Get Pending Count from Temp
	Update l SET l.PendingCount = t.PendingCount
	From @@tblTempAgentPendingLead t INNER JOIN  @@tblAgentPendingLead l ON t.AgentID = l.AgentID
	
	--****Call Back Due in Next 2 Hours to PendingCound****
	DELETE from @@tblTempAgentPendingLead
	
	INSERT INTO @@tblTempAgentPendingLead (AgentID, [Status], [PendingCount])
	select s.CallBackAgentID, l.[Status], Count(l.AgentLeadID) AS [PendingCount] 
	FROM tblCXCRMAgentLeads l 
	INNER JOIN tblCEUser u ON l.AgentID = u.CEID
	LEFT JOIN tblCXCRMCallStatus s ON l.AgentLeadID = s.AgentLeadID And l.AgentID = s.AgentID And l.CallType = s.CallTypeID
	Where u.[Status] = 1 AND u.UsrTypeID = 200 AND (u.Info1 = 'ONSS' OR u.Info1 = 'ONJ')
	AND (l.[Status] = 2) And Convert(varchar(8), l.CreatedDate, 112) > '20121101'
	And (l.CallType = 100 OR l.CallType = 101) 
	--And l.AgentID IN (45053, 4425, 547, 18808, 8543, 18813, 18818, 6807)
	And l.CustomerID > 0 And s.CallBackDateTime <= DateAdd(hour, 2, GetDate())
	Group By s.CallBackAgentID, l.[Status]
	
	Update l SET l.PendingCount = l.PendingCount + t.PendingCount
	From @@tblTempAgentPendingLead t INNER JOIN  @@tblAgentPendingLead l ON t.AgentID = l.AgentID
	--****END Call Back Due in Next 2 Hours to PendingCound****
	
	
	--****Follow Due in Next 2 Hours to PendingCound*****
	DELETE from @@tblTempAgentPendingLead
	
	INSERT INTO @@tblTempAgentPendingLead (AgentID, [Status], [PendingCount])
	select l.AgentID, l.[Status], Count(l.AgentLeadID) AS [PendingCount] 
	FROM tblCXCRMAgentLeads l
	INNER JOIN tblCEUser u ON l.AgentID = u.CEID
	Where u.[Status] = 1 AND u.UsrTypeID = 200 AND (u.Info1 = 'ONSS' OR u.Info1 = 'ONJ')
	AND (l.[Status] = 3) And Convert(varchar(8), l.CreatedDate, 112) > '20121101'
	And (l.CallType = 100 OR l.CallType = 101) 
	--And l.AgentID IN (45053, 4425, 547, 18808, 8543, 18813, 18818, 6807)
	And l.CustomerID > 0 And (l.ModifiedDate+1) <= DateAdd(hour, 2, GetDate())
	Group By l.AgentID, l.[Status]
	
	Update l SET l.PendingCount = l.PendingCount + t.PendingCount
	From @@tblTempAgentPendingLead t INNER JOIN  @@tblAgentPendingLead l ON t.AgentID = l.AgentID
	--****END Follow Due in Next 2 Hours to PendingCound*****
	
	--****POLICY NOT EXPIRE SO SOON *****
	INSERT INTO @@tblTempAgentPendingLead (AgentID, [Status], [PendingCount])
	Select  c.CallBackAgentID, l.[Status], Count(l.AgentLeadID) AS [PendingCount]
	from tblCXCRMAgentLeads l 
	INNER JOIN tblCXCRMCallStatus c ON l.AgentLeadID = c.AgentLeadID AND c.CallTypeID = l.CallType
	INNER JOIN tblCEUser u ON u.CEID = l.CustomerID		
	Where u.UsrTypeId = 400 AND l.Status = 100 
	And c.MainCallStatusTypeID = 7 And c.CallStatusTypeID = 10
	AND c.CallBackDateTime IS NOT NULL And c.CallBackAgentID IS NOT NULL
	And (DateDiff(Day, GETDATE(), c.CallBackDateTime) <= 45)
	Group By c.CallBackAgentID, l.[Status]
	
	Update l SET l.PendingCount = l.PendingCount + t.PendingCount
	From @@tblTempAgentPendingLead t INNER JOIN  @@tblAgentPendingLead l ON t.AgentID = l.AgentID
	--****END POLICY NOT EXPIRE SO SOON *****
	
	DELETE from @@tblTempAgentPendingLead
	
	--Broker 1 Pending Count
	Select @Broker1PendingCount = PendingCount from @@tblAgentPendingLead 
	WHERE AgentID = 547
	
	--Broker 8 Pending Count
	Select @Broker8PendingCount = PendingCount from @@tblAgentPendingLead 
	WHERE AgentID = 18818
	
	Select * from @@tblAgentPendingLead
	
	--Select @AgentID RandomAgentID
	

	--Select top 1 * FROM @@tblAgentPendingLead Where AgentID IN (18808, 8543, 18813, 8546) Order By PendingCount ASC

	Select @Broker1PendingCount Broker1PendingCount, @Broker8PendingCount Broker8PendingCount
	
	Declare curNewCustomer cursor for
	select CEID from tblCEUser Where (Convert(varchar(8), CreatedDate, 112) Between @OldStartDate And @OldEndDate)
	And (IsReject IS NULL OR IsReject = 0) And UsrTypeID = 400
	And CEID NOT IN (Select CustomerID from tblCXCRMAgentLeads Where (Convert(varchar(8), CreatedDate, 112) Between @LeadStartDate And @LeadEndDate))
	And CEID NOT IN (select RefCEUserID  from tblNonGGCarMoreDetails)
	Order By CEID

	OPEN curNewCustomer 
	FETCH NEXT FROM curNewCustomer INTO @CustomerID
	WHILE (@@fetch_status <> -1)
	BEGIN	

		Select top 1 @CarID = c.CarId from tblCarDetailTH c
		INNER JOIN tblCarPolicy cp ON c.CarID = cp.CECarID And c.RefCEUserID = cp.RefCEUserID
		Where c.RefCEUserId = @CustomerID 
		Order By CarId DESC
		
		IF(@CustomerID IS NOT NULL AND @CarID IS NOT NULL)
		BEGIN
			--IF @AgentID = 547 BEGIN SET @AgentID = 18808 END 
			--ELSE IF @AgentID = 18808 BEGIN SET @AgentID = 8543 END
			--ELSE IF @AgentID = 8543 BEGIN SET @AgentID = 18813 END
			--ELSE IF @AgentID = 18813 BEGIN SET @AgentID = 8546 END
			--ELSE IF @AgentID = 8546 BEGIN SET @AgentID = 18818 END
			--ELSE IF @AgentID = 18818 BEGIN SET @AgentID = 547 END
			
			Select TOP 1 @AgentID = AgentID FROM @@tblAgentPendingLead 
			--Where AgentID IN (45053, 4425, 18818, 18808, 8543, 18813) 
			WHERE AgentType = 'ONJ'
			AND PendingCount < 15
			Order By PendingCount ASC
			
			IF @AgentID > 0
			BEGIN
				INSERT INTO tblCXCRMAgentLeads(CarID, AgentID, CustomerID, CallType, Status, IsFromService)
				Select CarID, @AgentID, RefCEUserID, 100, 1, 0 from tblCarDetailTH Where RefCEUserId = @CustomerID And CarId = @CarID
				
				Update @@tblAgentPendingLead SET PendingCount = PendingCount + 1
				Where AgentID = @AgentID
				
				SET @AgentID = 0
			END
			ELSE 
				BREAK
			
		END
	FETCH NEXT FROM curNewCustomer INTO @CustomerID
	
	END
	
	CLOSE curNewCustomer
	DEALLOCATE curNewCustomer
	
	--Check available lead count per agent
	--select * from @@tblAgentPendingLead
	Select u.Info1, u.CEID, u.Email, l.PendingCount 
	from @@tblAgentPendingLead l INNER JOIN tblCEUser u
	ON l.AgentID = u.CEID
	
	-- Check today assignment
	Select Count(AgentLeadID), AgentID from tblCXCRMAgentLeads
	Where Convert(varchar(8), CreatedDate, 112) = Convert(varchar(8), GETDATE(), 112) And IsFromService = 0
	Group By AgentID
	
	